package com.zhang.learn.javaassist.service;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.zhang.learn.javaassist.dto.FileInfoDto;
import com.zhang.learn.javaassist.utill.CopyExcelUtil;
import com.zhang.learn.javaassist.utill.ExcelParam;
import com.zhang.learn.javaassist.utill.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * @author zhanghaolian
 * @date 2021/7/21 22:15
 */
@Component
public class ExcelProcesser {
    @Autowired
    private ExcelUtil excelUtil;

    /**
     * 获取excel文件的sheet的总数
     *
     * @param fileInfoDto
     * @throws IOException
     */
    public void getPageCount(FileInfoDto fileInfoDto) {
        try {
            Workbook workBook = null;
            if (fileInfoDto.getSuffix().equals("xlsx")) {
                FileInputStream fis = new FileInputStream(fileInfoDto.getSysPath());
                workBook = new XSSFWorkbook(fis);
                int numberOfSheets = workBook.getNumberOfSheets();
                fileInfoDto.setPageCount(numberOfSheets);
                fis.close();
            } else if (fileInfoDto.getSuffix().equals("xls")) {
                FileInputStream fis = new FileInputStream(fileInfoDto.getSysPath());
                workBook = new HSSFWorkbook(fis);
                int numberOfSheets = workBook.getNumberOfSheets();
                fileInfoDto.setPageCount(numberOfSheets);
                fis.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * excel导出处理的结果
     *
     * @param list 文件信息列表
     */
    public void outputResult(List<FileInfoDto> list) {
        try {
            //设置Excel的描述文件
            ExportParams exportParams = new ExportParams("office文件列表", "文件信息", ExcelType.XSSF);
            //进行导出的基本操作
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, FileInfoDto.class, list);
            //输入输出流地址
            FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\newtab\\Desktop\\dealResult.xlsx");
            //进行输出流
            workbook.write(fileOutputStream);
            //关流
            workbook.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * httpResponse 方式导出
     *
     * @param list
     * @param clazz
     * @param request
     * @param response
     */
    public void outputResult(List<FileInfoDto> list, Class clazz, HttpServletRequest request, HttpServletResponse response) {
        ExcelParam param = new ExcelParam();
        param.setRequest(request);
        param.setResponse(response);
        param.setDtoClass(clazz);
        param.setSheetName("sheet1");
        param.setFileName("office文件列表");
        param.setTitle("文件信息");
        param.setData(list);
        excelUtil.export(param);
    }

    public static void main(String[] args) {
        String srcPath = "C:\\Users\\newtab\\Desktop\\new\\生产环境-电子银行承兑汇票极速贴现业务承兑行清单0924-带行号.xlsx";
        String coverPath = "C:\\Users\\newtab\\Desktop\\new\\费用报销单.xlsx";
        String resultPath = "C:\\Users\\newtab\\Desktop\\new\\result.xlsx";
        mergerNewExcel(srcPath, coverPath, resultPath);
    }

    /**
     * EXCELL 1997-2003 替换首页
     *
     * @param srcPath    原文档路径
     * @param coverPath  封面文档路径
     * @param resultPath 结果文档输出路径
     */
    public void mergerOldExcel(String srcPath, String coverPath, String resultPath) {
        try (
                FileInputStream srcFis = new FileInputStream(srcPath);
                FileInputStream coverFis = new FileInputStream(coverPath);
                FileOutputStream srcFos = new FileOutputStream(resultPath);
        ) {
            HSSFWorkbook srcWorkbook = new HSSFWorkbook(srcFis);
            HSSFWorkbook coverWorkBook = new HSSFWorkbook(coverFis);
            HSSFSheet coverSheet = coverWorkBook.getSheetAt(1);
            HSSFSheet shouyeSheet = srcWorkbook.createSheet("首页");
            CopyExcelUtil.copySheet(srcWorkbook, coverSheet, shouyeSheet);
            srcWorkbook.setSheetOrder("首页", 0);
            srcWorkbook.write(srcFos);
            srcWorkbook.close();
            coverWorkBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * EXCELL 2007 替换首页
     *
     * @param srcPath    原文档路径
     * @param coverPath  封面文档路径
     * @param resultPath 结果文档输出路径
     */
    public static void mergerNewExcel(String srcPath, String coverPath, String resultPath) {
        try (
                FileInputStream srcFis = new FileInputStream(srcPath);
                FileInputStream coverFis = new FileInputStream(coverPath);
                FileOutputStream srcFos = new FileOutputStream(resultPath);
        ) {
            XSSFWorkbook scrWorkBook = new XSSFWorkbook(srcFis);
            XSSFWorkbook coverWorkBook = new XSSFWorkbook(coverFis);
            XSSFSheet coverSheet = coverWorkBook.getSheetAt(1);
            XSSFSheet shouyeSheet = scrWorkBook.createSheet("首页");
            CopyExcelUtil.copySheet(scrWorkBook, coverSheet, shouyeSheet);
            scrWorkBook.setSheetOrder("首页", 0);
            scrWorkBook.write(srcFos);
            scrWorkBook.close();
            coverWorkBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
